﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Dapper.Extensions.Common
{
    /// <summary>
    /// Sql生成器
    /// </summary>
    public class SqlGenerator
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="columnList"></param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GetInsertSqlValuesByEntity(object entity,string columnList) 
        {
      
            Type type = entity.GetType();
            PropertyInfo[] propertys = type.GetProperties();
            var listProperty = new List<PropertyInfo>(propertys);
            StringBuilder sbValues = new StringBuilder();
            var listColumn = columnList.Split(new string[] {","}, StringSplitOptions.RemoveEmptyEntries);
            foreach (var column in listColumn)
            {
                string colName = column.Replace("[", "").Replace("]", "");
                var prop = listProperty.FirstOrDefault(m => m.Name == colName);
                var oVal = prop.GetValue(entity);
                sbValues.Append(GetSqlValue(oVal));
                sbValues.Append(",");
            }
            return sbValues.ToString().TrimEnd(',');
        }
   
        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string GetSqlValue(object obj)
        {
            if (obj == null)
            {
                return "NULL";
            }
                
            if (
                      obj is string
                    ||obj is char
                )
            {
                return $"N'{obj.ToString().SqlFilter()}'";
            }
            else if (obj is DateTime)
            {
                DateTime dtObj = Convert.ToDateTime(obj);
                if (dtObj.IsNullDateTime())
                {
                    return "NULL";
                }
                else
                {
                    return $"'{dtObj.ToString("yyyy-MM-dd HH:mm:ss.fff")}'";
                }
            }
            else if (
                    obj is byte
                    || obj is short
                    || obj is int
                    || obj is long
                    || obj is double
                    || obj is float
                    || obj is decimal
                    )
            {
                return obj.ToString();
            }
            else if (obj is bool)
            {
                bool value = Convert.ToBoolean(obj);
                return value ? "1" : "0";
            }
            else if (obj is Guid)
            {
                return $"'{obj.ToString()}'";
            }
            return "NULL";
        }

        /// <summary>
        /// 获取更新sql语句的 "set"部分
        /// </summary>
        /// <param name="poco"></param>
        /// <param name="dicProperties"></param>
        /// <returns></returns>
        public static string GetSetSqlString(PocoData poco,IDictionary<string,object> dicProperties)
        {
            string szReturn = string.Empty;
            StringBuilder sb=new StringBuilder();
            foreach (var property in dicProperties)
            {
                var column = poco.Columns.FirstOrDefault(m => m.ColumnName == property.Key);
                if (column!=null)
                {
                    sb.AppendFormat($"{column.ColumnName}={GetSqlValue(property.Value)}");
                }
                sb.Append(",");
            }
            if (sb.Length > 0)
            {
                szReturn = " SET " + sb.ToString().TrimEnd(',');
            }
            else
            {
                szReturn = "";
            }
            return szReturn;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="poco"></param>
        /// <param name="entity"></param>
        /// <param name="toUpdateColumnNames"></param>
        /// <returns></returns>
        public static string GetSetSqlString(PocoData poco, object entity, List<string> toUpdateColumnNames )
        {
            string szReturn = string.Empty;
            StringBuilder sb = new StringBuilder();
            foreach (var columnName in toUpdateColumnNames)
            {
                var column = poco.Columns.FirstOrDefault(m => m.ColumnName == columnName);
                if (column != null)
                {
                    sb.AppendFormat($"{column.ColumnName}={GetSqlValue(GetColumnValue(entity, columnName))}");
                }
                sb.Append(",");
            }
            if (sb.Length > 0)
            {
                szReturn = " SET " + sb.ToString().TrimEnd(',');
            }
            else
            {
                szReturn = "";
            }
            return szReturn;
        }

        private static object GetColumnValue(object entity,string columnName)
        {
            object oVal = null;
            Type type = entity.GetType();
            PropertyInfo[] propertys = type.GetProperties();
            var listProperty = new List<PropertyInfo>(propertys);
            var prop = listProperty.FirstOrDefault(m => m.Name == columnName);
            if (prop != null)
            {
                oVal = prop.GetValue(entity);
            }
            return oVal;
        }
        /// <summary>
        /// 从表达式获取其成员名称列表
        /// </summary>
        /// <param name="propertyExpressions"></param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<string> GetMemberNameFromLamdaList<T>(Expression<Func<T, object>>[] propertyExpressions)
            where T : class
        {
            if (propertyExpressions == null)
            {
                return null;
            }

            var list = new List<string>();
            propertyExpressions.ToList().ForEach(m =>
            {
                MemberInfo member = ReflectionHelper.GetProperty(m);
                list.Add(member.Name);
            });

            return list;
        }

        /// <summary>
        /// 从谓词表达式获取其所有包含的成员名称
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static string GetMemberNameFromPredicate<T>(Expression<Func<T, object>> expression)
           where T : class
        {
            if (expression == null)
            {
                return string.Empty ;
            }

            MemberInfo member = ReflectionHelper.GetProperty(expression);

            return member.Name;
        }

        /// <summary>
        /// 从表达式获取其成员名称
        /// </summary>
        /// <param name="propertyExpressions"></param>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="M"></typeparam>
        /// <returns></returns>
        public static string GetMemberNameFromExpression<T,M>(Expression<Func<T, M>> propertyExpressions)
            where T: class
        {
            if (propertyExpressions == null)
            {
                return null;
            }

            MemberInfo member = ReflectionHelper.GetProperty(propertyExpressions);

            return member.Name;
        }

        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="poco"></param>
        /// <param name="list">集合</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(PocoData poco,IEnumerable<T> list)
        {
            var type = typeof(T);


            var properties = type.GetProperties().ToList();
            var tableName = poco.Table.TableName;
            var cols = poco.Columns;

            var newDt = new DataTable(tableName);

            poco.Columns.ForEach(m =>
            {
                newDt.Columns.Add(m.ColumnName, m.PropertyType);
            });

            foreach (var item in list)
            {
                var newRow = newDt.NewRow();

                cols.ForEach(col =>
                {
                    var oFind=properties.FirstOrDefault(m => m.Name == col.ColumnName);
                    if (oFind != null)
                    {
                        newRow[col.ColumnName] = oFind.GetValue(item, null) ?? DBNull.Value;
                    }
                });

                newDt.Rows.Add(newRow);
            }

            return newDt;
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="poco"></param>
        /// <param name="list">源数据</param>
        public static void BulkCopy<T>(IDbConnection conn,PocoData poco, IEnumerable<T> list)
        {
            var dt = ToDataTable(poco,list);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            using (var sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn))
            {
                sqlbulkcopy.DestinationTableName = dt.TableName;
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
            }
        }

    }
}
